﻿CREATE PROCEDURE [dbo].[kp_HoSoTimKiemTheoTuKhoa]
	@Tu_Khoa nvarchar(255),
	@Ma_Tt1 int,
	@Ma_Tt2 int,
	@Ma_Vt nvarchar(2),
	@Da_Tra int			
AS
	if @Tu_Khoa not like '%[^0-9]%' AND Len(IsNull(@Tu_Khoa,''))>0
	SELECT * FROM vHoSo 
			WHERE 
				(
					Ma_Tt=@Ma_Tt1 
					OR Ma_Tt=@Ma_Tt2
				)
			AND Ma_Vt=@Ma_Vt
			AND ISNULL(Da_Tra,0)= @Da_Tra
			AND	
				(--
					--	ISNULL(@Tu_Khoa,'')=''
						@Tu_Khoa = Ma_Hs
					OR	@Tu_Khoa = So_Gp
					OR	@Tu_Khoa = So_Bn
					OR	Ten_Hokd like N'%'+@Tu_Khoa+'%'
					OR	Dia_Diem like N'%'+@Tu_Khoa+'%'
					OR	Ho_Ten like	@Tu_Khoa
					
				)
else
	SELECT * FROM vHoSo 
			WHERE 
				(
					Ma_Tt=@Ma_Tt1 
					OR Ma_Tt=@Ma_Tt2
				)
			AND Ma_Vt=@Ma_Vt
			AND ISNULL(Da_Tra,0)= @Da_Tra
			AND	
				(
						ISNULL(@Tu_Khoa,'')=''
				--	OR	@Tu_Khoa = Ma_Hs
				--	OR	@Tu_Khoa = So_Gp
				--	OR	@Tu_Khoa = So_Bn
					OR	Ten_Hokd like N'%'+@Tu_Khoa+'%'
					OR	Dia_Diem like N'%'+@Tu_Khoa+'%'
					OR	Ho_Ten like	@Tu_Khoa
					
				)